﻿

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Statistics_Financial_CreateDaily_aotu_getlist]
	-- Add the parameters for the stored procedure here
	(
	@CompanyId INT ,
	@TDate	DATETIME
	)
AS
BEGIN

--SELECT 
--(SELECT 
----现金收入
--ISNULL(( SELECT SUM([Income]) FROM [Finance_RunningAccount] WHERE  (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId)) 
-- AND  createdate>= @TDate and createdate<=@TDate   AND CompanyId=@CompanyId),0)-
-- --现金支出
-- ISNULL((SELECT sum(outlayprice)
--  FROM [Finance_Outlay] 
--WHERE  (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId)) 
--  and CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10), @TDate,120)   AND companyidcur=@CompanyId),0)

----支出审核时生成的成本类支出不在现金银行中减少 ，按财务审核日期
--+isnull((SELECT SUM(costprice) FROM Project p WHERE CONVERT(VARCHAR(10), p.FinanceAuditDate,120)=CONVERT(VARCHAR(10), @TDate,120) AND p.CompanyId=@CompanyId
--AND p.FinanceAudit=2 AND ((Select PaymentTypeId From [Order] Where OId=p.OrderId and [order].CompanyId=@CompanyId)=1 
--   OR (Select PaymentTypeId From [Order] Where OId=p.OrderId and [order].CompanyId=@CompanyId AND [Order].BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and fb.CompanyId=@CompanyId) )=2 )),0)
--+
----昨日现金
--ISNULL((select IBA FROM Statistics_Financial  WHERE
-- CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10),DATEADD(D,-1, @TDate),120)   AND CompanyId=@CompanyId),0)+
-- --对冲
--ISNULL((SELECT sum(dch_xj) FROM Order_Change_Hedge WHERE CONVERT(VARCHAR(10), UpTime,120)=CONVERT(VARCHAR(10), @TDate,120) AND CompanyId=@CompanyId),0)
--+
----存现-提现
--(isnull((SELECT sum(cx) FROM txcx t WHERE CONVERT(VARCHAR(10),createdate,120)=CONVERT(VARCHAR(10), @TDate,120) AND CompanyId=@CompanyId AND  (t.cx_PaymentTypeId=1 or t.cx_BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId)) ),0)
--	-
--isnull((SELECT sum(tx) FROM txcx t WHERE CONVERT(VARCHAR(10),createdate,120)=CONVERT(VARCHAR(10), @TDate,120) AND CompanyId=@CompanyId AND  (t.tx_PaymentTypeId=1 or t.tx_BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId))),0)
--)
--) AS n1,(
	
--SELECT 
----银行收入
--ISNULL(( SELECT SUM(income) FROM [Finance_RunningAccount] WHERE (
--	 PaymentTypeId=3 OR 
--(BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId) )
--)

--  AND CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10), @TDate,120)   AND CompanyId=@CompanyId),0)-
----银行支出
--ISNULL((SELECT sum(outlayprice)
--  FROM [Finance_Outlay] 
--WHERE  (
--	 PaymentTypeId=3 OR 
--(BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId) )
--)  and CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10), @TDate,120)   AND companyidcur=@CompanyId),0)
----支出审核时生成的成本类支出不在现金银行中减少
--+isnull((SELECT SUM(costprice) FROM Project p WHERE CONVERT(VARCHAR(10), p.FinanceAuditDate,120)=CONVERT(VARCHAR(10), @TDate,120) AND p.CompanyId=@CompanyId
--AND p.FinanceAudit=2 AND (
--	(Select PaymentTypeId From [Order] Where OId=p.OrderId and [order].CompanyId=@CompanyId)=3 OR 
--(Select PaymentTypeId From [Order] Where OId=p.OrderId and [order].CompanyId=@CompanyId AND [Order].BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId) )=2
-- )),0)
--+
----昨日银行
--ISNULL((select IBB FROM Statistics_Financial  WHERE
-- CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10),DATEADD(D,-1, @TDate),120)   AND CompanyId=@CompanyId),0)+
-- --对冲
--ISNULL((SELECT sum(dch_yy) FROM Order_Change_Hedge WHERE CONVERT(VARCHAR(10), UpTime,120)=CONVERT(VARCHAR(10), @TDate,120) AND CompanyId=@CompanyId),0) 
--+
----存现-提现
--(isnull((SELECT sum(cx) FROM txcx t WHERE CONVERT(VARCHAR(10),createdate,120)=CONVERT(VARCHAR(10), @TDate,120) AND CompanyId=@CompanyId AND  (
--	t.cx_PaymentTypeId =3 OR 
--t.cx_BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId) 
-- )   ),0)
--	-
--isnull((SELECT sum(tx) FROM txcx t WHERE CONVERT(VARCHAR(10),createdate,120)=CONVERT(VARCHAR(10), @TDate,120) AND CompanyId=@CompanyId AND   (
--	t.tx_PaymentTypeId=3 OR 
--t.tx_BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId)
-- )   ),0)
--)
--) AS n2,(

----账户余额
--select
--isnull((SELECT SUM(ye) AS ye FROM (
--SELECT (  SELECT  TOP 1 ye From AB_List c Where c.Iid =ai.id AND
--   Convert(varchar(10),c.UpTime,120)<=CONVERT(VARCHAR(10), @TDate,120) And AB_Type=0 AND 
--   c.CompanyId=@CompanyId Order By c.UpTime DESC) AS ye 

--FROM AB_Interface ai WHERE  syzh<>0 AND ai.CompanyId=@CompanyId) AS a),0)+
----对冲
--ISNULL((SELECT sum(dch_zhh) FROM Order_Change_Hedge WHERE CONVERT(VARCHAR(10), UpTime,120)=CONVERT(VARCHAR(10), @TDate,120) AND CompanyId=@CompanyId),0) 
	 
--) AS n3


	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, Convert(varchar(10),@TDate,120) + ' 00:00:00')
	Set @eRq = Convert(Datetime, Convert(varchar(10),@TDate,120) + ' 23:59:59')



SELECT 
(SELECT 
--现金收入
ISNULL(( SELECT SUM([Income]) FROM [Finance_RunningAccount] WHERE CompanyId=@CompanyId  AND createdate>=@sRq and createdate<=@eRq 
 AND (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId)) 
 ),0)-
 --现金支出
 ISNULL((SELECT sum(outlayprice)
  FROM [Finance_Outlay] 
WHERE  companyidcur=@CompanyId  and createdate>=@sRq and createdate<=@eRq   AND (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId)) 
 ),0)

--支出审核时生成的成本类支出不在现金银行中减少
+isnull((SELECT SUM(costprice) FROM Project p WHERE  p.CompanyId=@CompanyId AND p.FinanceAudit=2 AND p.FinanceAuditDate>=@sRq and p.FinanceAuditDate<= @eRq
 AND ((Select PaymentTypeId From [Order] Where OId=p.OrderId)=1 
   OR (Select PaymentTypeId From [Order] Where OId=p.OrderId AND [Order].BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1) )=2 )),0)
+
--昨日现金
ISNULL((select IBA FROM Statistics_Financial  WHERE  CompanyId=@CompanyId AND 
  createdate>=dateadd(d,-1,@sRq) and createdate<=dateadd(d,-1,@eRq) ),0)+
 --对冲
ISNULL((SELECT sum(dch_xj) FROM Order_Change_Hedge WHERE CompanyId=@CompanyId AND UpTime>=@sRq and UpTime<=@sRq ),0)
+
--存现-提现
(isnull((SELECT sum(cx) FROM txcx t WHERE CompanyId=@CompanyId AND createdate>=@sRq and createdate<=@eRq  AND  (t.cx_PaymentTypeId=1 or t.cx_BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId)) ),0)
	-
isnull((SELECT sum(tx) FROM txcx t WHERE CompanyId=@CompanyId AND createdate>=@sRq and createdate<=@eRq  AND  (t.tx_PaymentTypeId=1 or t.tx_BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId))),0)
)
) AS n1,(
	
SELECT 
--银行收入
ISNULL(( SELECT SUM(income) FROM [Finance_RunningAccount] WHERE CompanyId=@CompanyId AND (
	  PaymentTypeId=3 OR 
(BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId) )
)

  AND createdate>=@sRq and createdate<=@eRq  ),0)-
--银行支出
ISNULL((SELECT sum(outlayprice)
  FROM [Finance_Outlay] 
WHERE  companyidcur=@CompanyId and createdate>=@sRq and createdate<=@eRq    AND (
	 PaymentTypeId=3 OR 
(BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId) )
)  ),0)
--支出审核时生成的成本类支出不在现金银行中减少
+isnull((SELECT SUM(costprice) FROM Project p WHERE p.CompanyId=@CompanyId AND  p.FinanceAuditDate>=@sRq and p.FinanceAuditDate<=@eRq 
AND p.FinanceAudit=2 AND (
	(Select PaymentTypeId From [Order] Where OId=p.OrderId)=3 OR 
(Select PaymentTypeId From [Order] Where OId=p.OrderId AND [Order].BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId) )=2
 )),0)
+
--昨日银行
ISNULL((select IBB FROM Statistics_Financial  WHERE CompanyId=@CompanyId  AND 
  createdate>=dateadd(d,-1,@sRq) and createdate<=dateadd(d,-1,@eRq)),0)+
 --对冲
ISNULL((SELECT sum(dch_yy) FROM Order_Change_Hedge WHERE CompanyId=@CompanyId  AND UpTime>=@sRq and UpTime<=@eRq),0) 
+
--存现-提现
(isnull((SELECT sum(cx) FROM txcx t WHERE CompanyId=@CompanyId AND  createdate>=@sRq and createdate<=@eRq AND (
	t.cx_PaymentTypeId =3 OR 
t.cx_BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId) 
 )   ),0)
	-
isnull((SELECT sum(tx) FROM txcx t WHERE CompanyId=@CompanyId AND  createdate>=@sRq and createdate<=@eRq AND  (
	t.tx_PaymentTypeId=3 OR 
t.tx_BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId)
 )   ),0)
)
) AS n2,(

--账户余额
select
isnull((SELECT SUM(ye) AS ye FROM (
SELECT (  SELECT  TOP 1 ye From AB_List c Where c.CompanyId=@CompanyId And AB_Type=0 AND c.Iid =ai.id AND
   c.UpTime<=@eRq 
   Order By c.UpTime DESC) AS ye 

FROM AB_Interface ai WHERE  syzh<>0 AND ai.CompanyId=@CompanyId) AS a),0)+
--对冲
ISNULL((SELECT sum(dch_zhh) FROM Order_Change_Hedge WHERE  CompanyId=@CompanyId  AND UpTime>=@sRq and UpTime<=@eRq),0) 
	 
) AS n3
END



